package cn.gbase.jiangsu.data.transfer.service;

import java.math.BigDecimal;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.nutz.dao.Chain;
import org.nutz.dao.Cnd;
import org.nutz.dao.Dao;
import org.nutz.dao.QueryResult;
import org.nutz.dao.pager.Pager;
import org.nutz.dao.util.cri.SqlExpressionGroup;
import org.nutz.ioc.aop.Aop;
import org.nutz.ioc.loader.annotation.Inject;
import org.nutz.ioc.loader.annotation.IocBean;
import org.nutz.mvc.Mvcs;
import org.nutz.service.IdEntityService;

import cn.gbase.jiangsu.data.transfer.action.EventAction;
import cn.gbase.jiangsu.data.transfer.bean.BuildingUnit;
import cn.gbase.jiangsu.data.transfer.bean.Community;
import cn.gbase.jiangsu.data.transfer.bean.CommunityBuilding;
import cn.gbase.jiangsu.data.transfer.bean.Company;
import cn.gbase.jiangsu.data.transfer.bean.CompanyToBuilding;
import cn.gbase.jiangsu.data.transfer.bean.CompanyToSchoolUser;
import cn.gbase.jiangsu.data.transfer.bean.CompanyUser;
import cn.gbase.jiangsu.data.transfer.bean.ShopUser;
import cn.gbase.jiangsu.data.transfer.bean.ShopUserView;
import cn.gbase.jiangsu.data.transfer.bean.SystemUser;
import cn.gbase.jiangsu.data.transfer.bean.UnitRoom;
import cn.gbase.jiangsu.data.transfer.common.Constants;
import cn.gbase.jiangsu.data.transfer.dto.EventParam;
import cn.gbase.jiangsu.data.transfer.util.CommonUtil;
import cn.gbase.jiangsu.data.transfer.util.DefaultPasswordEncoder;

@IocBean(args = { "refer:dao" })
public class CompanyUserService extends IdEntityService<CompanyUser> {
	@Inject
	private BuildingService buildingService;
	@Inject("java:$config.get('excelDownPath')")
	private String excelDownPath;
	@Inject
	private EventAction eventAction;

	public CompanyUserService() {
		super();
	}

	public CompanyUserService(Dao dao) {
		super(dao);
	}

	/**
	 * 更新密码.
	 * 
	 * @param user
	 * @return
	 */
	public void updatePWS(CompanyUser user) {

		// 密码加密
		String encodePass = new DefaultPasswordEncoder("MD5").encode(user.getNewPassword());

		// 更新
		this.dao().update(CompanyUser.class, Chain.make("password", encodePass), Cnd.where("id", "=", user.getId()));
	}

	/**
	 * 根据ID取得房间.
	 * 
	 * @param roomId
	 * @return
	 */
	public UnitRoom getRoom(int roomId) {
		return this.dao().fetch(UnitRoom.class, roomId);
	}

	/**
	 * 根据ID取得单元.
	 * 
	 * @param unitId
	 * @return
	 */
	public BuildingUnit getUnit(int unitId) {
		return this.dao().fetch(BuildingUnit.class, unitId);
	}

	/**
	 * 取得房间的地址.
	 * 
	 * @param roomId
	 *            房间ID
	 * @return 地址
	 */
	public String getRoomAddress(int roomId) {

		// 房间
		UnitRoom room = this.getRoom(roomId);
		if (room == null) {
			return "";
		}

		// 单元
		BuildingUnit unit = this.getUnit(room.getUnitId());

		// 楼
		CommunityBuilding building = this.getBuilding(unit.getBuildingId());

		// 社区
		Community com = this.getCommunity(building.getCommunityId());

		// xx小区12-1-202
		return com.getCommunityName() + building.getBuildingNo() + "-" + unit.getUnitNo() + "-" + room.getRoomNo();
	}

	/**
	 * 根据ID取得社区.
	 * 
	 * @param CommunityId
	 * @return
	 */
	public Community getCommunity(int CommunityId) {
		return this.dao().fetch(Community.class, CommunityId);
	}

	/**
	 * 根据ID取得楼.
	 * 
	 * @param buildingId
	 *            楼ID
	 * @return
	 */
	public CommunityBuilding getBuilding(int buildingId) {
		return this.dao().fetch(CommunityBuilding.class, buildingId);
	}

	/**
	 * 根据条件判断房间是否已经存在（已删除的不算）.
	 * 
	 * @param communityId
	 *            社区ID
	 * @param buildingNo
	 *            楼号
	 * @param unitNo
	 *            单元号
	 * @param roomNo
	 *            房间号
	 * @return
	 */
	public boolean isRoomExist(int communityId, String buildingNo, String unitNo, String roomNo) {
		return this.dao().count(
				UnitRoom.class,
				Cnd.where("communityId", "=", communityId).and("buildingNo", "=", buildingNo)
						.and("unitNo", "=", unitNo).and("roomNo", "=", roomNo).and("deleteFlag", "=", 0)) > 0;
	}

	/**
	 * 取得房间列表.
	 * 
	 * @param pageNumber
	 * @param name
	 * @return
	 */
	public QueryResult getRoomList(int pageNumber, String communityId, String buildingNo, String unitNo, String roomNo) {

		if (pageNumber <= 0) {
			pageNumber = 1;
		}

		// 拼接条件（只显示房间未删除的）
		SqlExpressionGroup group = Cnd.exps("deleteFlag", "=", 0);
		if (communityId != null && communityId.length() > 0) {
			group = group.and("communityId", "=", Integer.valueOf(communityId));
		}
		if (buildingNo != null && buildingNo.length() > 0) {
			group = group.and("buildingNo", "=", buildingNo);
		}
		if (unitNo != null && unitNo.length() > 0) {
			group = group.and("unitNo", "=", unitNo);
		}
		if (roomNo != null && roomNo.length() > 0) {
			group = group.and("roomNo", "=", roomNo);
		}

		// 最大页数
		Pager pager = this.dao().createPager(pageNumber, Constants.LIST_ONE_PAGE_SIZE);

		// 查询记录总数
		int recordCount = this.dao().count(UnitRoom.class, Cnd.where(group));
		pager.setRecordCount(recordCount);

		// 没数据，直接返回
		if (recordCount == 0) {
			return new QueryResult(new ArrayList<UnitRoom>(0), pager);
		}

		// 计算页数
		int maxPageNumber = 0;
		if (recordCount % Constants.LIST_ONE_PAGE_SIZE == 0) {
			maxPageNumber = recordCount / Constants.LIST_ONE_PAGE_SIZE;
		} else {
			maxPageNumber = recordCount / Constants.LIST_ONE_PAGE_SIZE + 1;
		}
		if (pageNumber > maxPageNumber) {
			pager.setPageNumber(maxPageNumber);
		}

		// 查询
		List<UnitRoom> resultList = this.dao().query(UnitRoom.class,
				Cnd.where(group).asc("communityCode").asc("buildingCode").asc("unitCode").asc("roomCode"), pager); // 当前页面显示记录

		// 每个房间的完整Code
		for (UnitRoom room : resultList) {
			String fullCode = room.getCommunityCode() + room.getBuildingCode() + room.getUnitCode()
					+ room.getRoomCode();
			room.setFullCode(fullCode);
		}

		// 返回
		return new QueryResult(resultList, pager);
	}

	/**
	 * 添加房间（楼号、单元号不存在时，自动添加）.
	 * 
	 * @param room
	 *            房间
	 * @return
	 */
	public UnitRoom addRoom(UnitRoom room) {

		// 取得或添加楼
		CommunityBuilding building = this.getOrCreateBuilding(room.getCommunityId(), room.getBuildingNo());

		// 取得或添加单元
		BuildingUnit unit = this.getOrCreateUnit(building.getId(), room.getUnitNo());

		// 插入房间
		room.setUnitId(unit.getId()); // 单元ID
		room.setAddtime(new Date());
		//room.setCreateUser(CommonUtil.getLoginUserName()); //没啥用
		room.setRoomCode(CommonUtil.formatRoomNo0000(room.getRoomNo())); // 房间编码，4位
		room.setDeleteFlag(0);
		this.dao().insert(room);
		return room;
	}

	/**
	 * 取得楼，不存在时则创建.
	 * 
	 * @return
	 */
	public CommunityBuilding getOrCreateBuilding(int communityId, String buildingNo) {

		// 取得或添加楼
		CommunityBuilding building = this.getBuildingByNo(communityId, buildingNo);
		if (building == null) {
			building = new CommunityBuilding();
			building.setBuildingNo(buildingNo); // 楼号
			building.setBuildingCode(CommonUtil.formatBuildingNo(buildingNo)); // 楼编码
			building.setCommunityId(communityId); // 社区ID
			building.setAddtime(new Date());
			//building.setCreateUser(CommonUtil.getLoginUserName()); //没啥用，注释掉了
			building.setDeleteFlag(0);
			building = this.dao().insert(building);
		}
		return building;
	}

	/**
	 * 根据楼号取得楼.
	 * 
	 * @param communityId
	 *            社区ID
	 * @param buildingNo
	 *            楼号
	 * @return
	 */
	public CommunityBuilding getBuildingByNo(int communityId, String buildingNo) {
		return this.dao()
				.fetch(CommunityBuilding.class,
						Cnd.where("communityId", "=", communityId).and("buildingNo", "=", buildingNo)
								.and("deleteFlag", "=", 0));
	}

	/**
	 * 取得单元，不存在时则创建.
	 * 
	 * @return
	 */
	private BuildingUnit getOrCreateUnit(int buildingId, String unitNo) {

		// 取得或添加单元
		BuildingUnit unit = this.getUnitByNo(buildingId, unitNo);
		if (unit == null) {
			unit = new BuildingUnit();
			unit.setBuildingId(buildingId); // 楼ID
			unit.setUnitNo(unitNo); // 单元号
			unit.setUnitCode(CommonUtil.formatUnitNo(unitNo)); // 单元编码
			unit.setAddtime(new Date());
			//unit.setCreateUser(CommonUtil.getLoginUserName()); //没啥用
			unit.setDeleteFlag(0);
			unit = this.dao().insert(unit);
		}
		return unit;
	}

	/**
	 * 根据单元号取得单元.
	 * 
	 * @param unitId
	 * @return
	 */
	public BuildingUnit getUnitByNo(int buildingId, String unitNo) {
		return this.dao().fetch(BuildingUnit.class,
				Cnd.where("buildingId", "=", buildingId).and("unitNo", "=", unitNo).and("deleteFlag", "=", 0));
	}

	/**
	 * 注册用户.
	 * 
	 * @param user
	 *            用户
	 */
	public void regUser(ShopUser user) {
		// 设置属性
		user.setPw(CommonUtil.encodePw(user.getPw())); // 加密
		user.setRoomId(user.getRoomId()); // 前台传了
		user.setShopPoint(0);
		user.setLoginTimes(0);
		user.setAddtime(new Date());
		user.setUserLevel(1);
		user.setAccountBalance(BigDecimal.ZERO); // 账户余额0

		// 插入
		this.dao().insert(user);
		//是校区用户，要绑定校区
		if (user.getUserType() == 2) {
			CompanyToSchoolUser cts = new CompanyToSchoolUser();
			cts.setCompanyId(user.getCompanyId());
			cts.setUserId(user.getId());
			this.dao().insert(cts);
		}
		// 注册送优惠券
		EventParam param = new EventParam();
		param.setEventName("用户注册");
		param.setUserId(user.getId());
		eventAction.triggerEvent(param);

	}

	/**
	 * 验证注册码是否有效.
	 */
	public Map<String, String> validateCode(String code) {

		// 验证绑定码
		UnitRoom room = this.dao().fetch(UnitRoom.class, Cnd.where("id", "=", code));
		if (room == null) {
			Map<String, String> m = new HashMap<String, String>();
			m.put("result", "NG");
			m.put("message", "无此房间。");
			return m;
		}

		int count = this.dao().count(ShopUser.class, Cnd.where("roomId", "=", code).and("deleteFlag", "=", 0));
		if (count > 0) {
			Community communityByRoom = buildingService.getCommunityByRoom(Integer.valueOf(code));
			if (0 == communityByRoom.getAllowOneRoomMultiUser()) {
				Map<String, String> m = new HashMap<String, String>();
				m.put("result", "NG");
				m.put("message", "该房间已经绑定用户。");
				return m;
			}
		}

		// 小区的完整名字：xx小区10-2-103
		String address = this.getRoomAddress(room.getId());

		// 返回消息
		Map<String, String> m = new HashMap<String, String>();
		m.put("result", "OK");
		m.put("message", address);
		return m;
	}

	/**
	 * 取得用户列表.
	 * 
	 * @param pageNumber
	 * @param username
	 * @param realname
	 * @return
	 */
	public QueryResult getUserList(int pageNumber, String username, String realname, int cid, int uid) {

		if (pageNumber <= 0) {
			pageNumber = 1;
		}
		// 拼接条件
		SqlExpressionGroup group = Cnd.exps("1", "=", 1);
		if (username != null && username.length() > 0) {
			group = group.and("username", "like", "%" + username + "%");
		}
		if (realname != null && realname.length() > 0) {
			group = group.and("remark", "like", "%" + realname + "%");
		}
		group = group.and("status", "<>", 0); //不是被删除的
		group = group.and("companyId", "=", cid); //物业ID
		group = group.and("id", "<>", uid); //去除登录人ID
		// 最大页数
		Pager pager = this.dao().createPager(pageNumber, Constants.LIST_ONE_PAGE_SIZE);

		// 查询记录总数
		int recordCount = this.dao().count(CompanyUser.class, Cnd.where(group));
		pager.setRecordCount(recordCount);

		// 没数据，直接返回
		if (recordCount == 0) {
			return new QueryResult(new ArrayList<CompanyUser>(0), pager);
		}

		// 计算页数
		int maxPageNumber = 0;
		if (recordCount % Constants.LIST_ONE_PAGE_SIZE == 0) {
			maxPageNumber = recordCount / Constants.LIST_ONE_PAGE_SIZE;
		} else {
			maxPageNumber = recordCount / Constants.LIST_ONE_PAGE_SIZE + 1;
		}
		if (pageNumber > maxPageNumber) {
			pager.setPageNumber(maxPageNumber);
		}

		// 查询
		List<CompanyUser> resultList = this.dao().query(CompanyUser.class, Cnd.where(group).desc("id"), pager); // 当前页面显示记录

		// 返回
		return new QueryResult(resultList, pager);

	}

	/**
	 * 添加或更新用户.
	 * 
	 * @param user
	 * @return
	 */

	@Aop("validationInterceptor")
	public boolean addOrUpdUser(CompanyUser user) {
		if (user.getId() > 0) {
			return updateUser(user);
		}
		return addUser(user);
	}

	/**
	 * 添加用户.
	 * 
	 * @param user
	 * @return
	 */
	public boolean addUser(CompanyUser user) {

		// 检查用户是否存在
		CompanyUser dbUser = this.dao().fetch(CompanyUser.class, Cnd.where("username", "=", user.getUsername()));
		if (dbUser != null) {
			return false;
		}
		// 当前登录用户
		SystemUser loginUser = (SystemUser) Mvcs.getReq().getSession().getAttribute("user");
		if (loginUser != null)
			//设置字段信息
			user.setCreateUser(loginUser.getUsername());

		else {
			CompanyUser loginCompany = (CompanyUser) Mvcs.getReq().getSession().getAttribute("companyUser");
			user.setCreateUser(loginCompany.getUsername());
		}
		user.setAddtime(new Date());
		user.setStatus(2);

		user.setPassword(new DefaultPasswordEncoder("MD5").encode(user.getPassword()));
		//插入DB
		this.dao().insert(user);
		return true;
	}

	/**
	 * 更新用户.
	 * 
	 * @param user
	 * @return
	 */
	public boolean updateUser(CompanyUser user) {
		// 检查是否存在
		CompanyUser dbUser = this.dao().fetch(CompanyUser.class,
				Cnd.where("username", "=", user.getUsername()).and("id", "<>", user.getId()));
		if (dbUser != null) {
			return false;
		}

		dbUser = this.dao().fetch(CompanyUser.class, user.getId());

		//更新字段
		dbUser.setAclStr(user.getAclStr());
		dbUser.setRemark(user.getRemark());
		dbUser.setUsername(user.getUsername());

		dbUser.setPassword(new DefaultPasswordEncoder("MD5").encode(user.getPassword()));
		//更新DB
		this.dao().update(dbUser);
		return true;
	}

	/**
	 * 根据ID取得用户.
	 * 
	 * @param userId
	 * @return
	 */
	public CompanyUser getUser(int userId) {
		return this.dao().fetch(CompanyUser.class, userId);
	}

	/**
	 * 删除用户.
	 * 
	 * @param userId
	 */
	public void deleteUser(int userId) {
		CompanyUser user = this.dao().fetch(CompanyUser.class, userId);
		user.setStatus(0);
		this.dao().update(user);
	}

	/**
	 * 确定用户.
	 * 
	 * @param userId
	 */
	public ShopUserView checkUser(String communityId, String buildNo, String unitNo, String roomNo, String mobile) {
		// 拼接条件
		SqlExpressionGroup group = Cnd.exps("1", "=", 1);
		if (communityId != null && communityId.length() > 0) {
			group = group.and("communityId", "=", communityId);
		}
		if (buildNo != null && buildNo.length() > 0) {
			group = group.and("buildingId", "=", buildNo);
		}
		if (unitNo != null && unitNo.length() > 0) {
			group = group.and("unitId", "=", unitNo);
		}
		if (roomNo != null && roomNo.length() > 0) {
			group = group.and("roomId", "=", roomNo);
		}
		if (mobile != null && mobile.length() > 0) {
			group = group.and("mobile", "=", mobile);
		}
		group = group.and("deleteFlag", "<>", 1); //不是被删除的
		List<ShopUserView> resultList = this.dao().query(ShopUserView.class, Cnd.where(group).desc("id")); // 当前页面显示记录
		if (resultList != null && resultList.size() > 0)
			return resultList.get(0);
		return null;
	}

	public String exportUser(Integer companyId, String communityId, String buildNo, String unitNo, String roomNo) {
		List<CompanyToBuilding> resultList = this.dao().query(CompanyToBuilding.class,
				Cnd.where("companyId", "=", companyId)); // 当前页面显示记录
		List<Integer> builds = new ArrayList<Integer>();
		for (CompanyToBuilding ctb : resultList)
			builds.add(ctb.getBuildingId());
		// 拼接条件
		SqlExpressionGroup group = Cnd.exps("1", "=", 1);
		group = group.and("buildingId", "in", builds);
		if (communityId != null && communityId.length() > 0) {
			group = group.and("communityId", "=", communityId);
		}
		if (buildNo != null && buildNo.length() > 0) {
			group = group.and("buildingId", "=", buildNo);
		}
		if (unitNo != null && unitNo.length() > 0) {
			group = group.and("unitId", "=", unitNo);
		}
		if (roomNo != null && roomNo.length() > 0) {
			group = group.and("roomId", "=", roomNo);
		}
		group = group.and("deleteFlag", "<>", 1); //不是被删除的
		List<ShopUserView> resultLists = this.dao().query(ShopUserView.class, Cnd.where(group).desc("id"), null);
		SimpleDateFormat sdf = new SimpleDateFormat("SSS");
		String folder = String.valueOf(sdf.format(new Date())); // 登陆名后面加上传的日期

		String path = excelDownPath + folder; // 路径是存文件的路径

		if (!resultLists.isEmpty()) {
			for (ShopUserView user : resultLists) {
				String fullCode = user.getCommunityCode() + user.getBuildingCode() + user.getUnitCode()
						+ user.getRoomCode();
				user.setFullCode(fullCode);
			}

			// 日期格式
			DateFormat dtf = new SimpleDateFormat("yyyy-MM-dd");

			// 生成HTML
			StringBuilder sb = new StringBuilder();
			sb.append("<meta http-equiv=\"Content-Type\" content=\"textml; charset=utf-8\" />");
			sb.append("<table border='1'>");
			sb.append("<tr style='background-color:#ccc'><td width='100'>编码</td>");
			sb.append("<td width='100'>社区</td>");
			sb.append("<td width='100'>楼号</td>");
			sb.append("<td width='100'>单元号</td>");
			sb.append("<td width='100'>房间号</td>");
			sb.append("<td width='100'>用户名</td>");
			sb.append("<td width='100'>真实姓名</td>");
			sb.append("<td width='100'>积分</td>");
			sb.append("<td width='100'>账户余额</td>");
			sb.append("<td width='100'>性别</td>");
			sb.append("<td width='100'>生日</td>");
			sb.append("<td width='100'>固定电话</td>");
			sb.append("<td width='100'>手机</td>");
			sb.append("<td width='100'>家庭人数</td>");
			sb.append("<td width='100'>职业</td>");
			sb.append("<td width='100'>兴趣</td>");
			sb.append("</tr>");
			for (ShopUserView user : resultLists) {
				sb.append("<tr><td style='mso-number-format:\"\\@\";'>");
				sb.append(user.getFullCode());
				sb.append("</td><td style='mso-number-format:\"\\@\";'>");
				sb.append(user.getCommunityName());
				sb.append("</td><td style='mso-number-format:\"\\@\";'>");
				sb.append(user.getBuildingNo());
				sb.append("</td><td style='mso-number-format:\"\\@\";'>");
				sb.append(user.getUnitNo());
				sb.append("</td><td style='mso-number-format:\"\\@\";'>");
				sb.append(user.getRoomNo());
				sb.append("</td><td style='mso-number-format:\"\\@\";'>");
				sb.append(user.getUsername());
				sb.append("</td><td style='mso-number-format:\"\\@\";'>");
				sb.append(user.getRealName());
				sb.append("</td><td style='mso-number-format:\"\\@\";' align='right'>");
				sb.append(user.getShopPoint());
				sb.append("</td><td style='mso-number-format:\"\\@\";' align='right'>");
				sb.append(user.getAccountBalance());
				sb.append("</td><td style='mso-number-format:\"\\@\";'>");
				sb.append(user.getSex() == 0 ? "男" : "女");
				sb.append("</td><td style='mso-number-format:\"\\@\";'>");
				if (user.getBirthday() == null) {
					sb.append("&nbsp;");
				} else {
					try {
						sb.append(dtf.format(user.getBirthday()));
					} catch (Exception e) {
					}
				}
				sb.append("</td><td style='mso-number-format:\"\\@\";'>");
				sb.append(user.getTelephone());
				sb.append("</td><td style='mso-number-format:\"\\@\";'>");
				sb.append(user.getMobile());
				sb.append("</td><td style='mso-number-format:\"\\@\";'>");
				sb.append(user.getFamilyNumber());
				sb.append("</td><td style='mso-number-format:\"\\@\";'>");
				sb.append(user.getOccupation());
				sb.append("</td><td style='mso-number-format:\"\\@\";'>");
				sb.append(user.getInterest());
				sb.append("</td></tr>");
			}
			sb.append("</table>");
			return sb.toString().replaceAll("null</td>", "&nbsp;</td>"); // null替换掉
		}
		return null;

	}

	public String exportSchoolUser(Integer companyId, String communityId, String buildNo, String unitNo, String roomNo) {
		Company company = this.dao().fetch(Company.class, companyId); // 当前页面显示记录

		// 拼接条件
		SqlExpressionGroup group = Cnd.exps("1", "=", 1);
		if (communityId != null && communityId.length() > 0) {
			group = group.and("communityId", "=", communityId);
		} else
			group = group.and("communityId", "=", company.getCommunityId());
		if (buildNo != null && buildNo.length() > 0) {
			group = group.and("buildingId", "=", buildNo);
		}
		if (unitNo != null && unitNo.length() > 0) {
			group = group.and("unitId", "=", unitNo);
		}
		if (roomNo != null && roomNo.length() > 0) {
			group = group.and("roomId", "=", roomNo);
		}
		group = group.and("deleteFlag", "<>", 1); //不是被删除的
		List<ShopUserView> resultLists = this.dao().query(ShopUserView.class, Cnd.where(group).desc("id"), null);
		SimpleDateFormat sdf = new SimpleDateFormat("SSS");
		String folder = String.valueOf(sdf.format(new Date())); // 登陆名后面加上传的日期

		String path = excelDownPath + folder; // 路径是存文件的路径

		if (!resultLists.isEmpty()) {
			for (ShopUserView user : resultLists) {
				String fullCode = user.getCommunityCode() + user.getBuildingCode() + user.getUnitCode()
						+ user.getRoomCode();
				user.setFullCode(fullCode);
			}

			// 日期格式
			DateFormat dtf = new SimpleDateFormat("yyyy-MM-dd");

			// 生成HTML
			StringBuilder sb = new StringBuilder();
			sb.append("<meta http-equiv=\"Content-Type\" content=\"textml; charset=utf-8\" />");
			sb.append("<table border='1'>");
			sb.append("<tr style='background-color:#ccc'><td width='100'>一卡通</td>");
			sb.append("<td width='100'>校区</td>");
			sb.append("<td width='100'>楼号</td>");
			sb.append("<td width='100'>单元号</td>");
			sb.append("<td width='100'>房间号</td>");
			sb.append("<td width='100'>用户名</td>");
			sb.append("<td width='100'>真实姓名</td>");
			sb.append("<td width='100'>积分</td>");
			sb.append("<td width='100'>账户余额</td>");
			sb.append("<td width='100'>性别</td>");
			sb.append("<td width='100'>生日</td>");
			sb.append("<td width='100'>固定电话</td>");
			sb.append("<td width='100'>手机</td>");
			sb.append("<td width='100'>家庭人数</td>");
			sb.append("<td width='100'>职业</td>");
			sb.append("<td width='100'>兴趣</td>");
			sb.append("</tr>");
			for (ShopUserView user : resultLists) {
				sb.append("<tr><td style='mso-number-format:\"\\@\";'>");
				sb.append(user.getOneCardNo());
				sb.append("</td><td style='mso-number-format:\"\\@\";'>");
				sb.append(user.getCommunityName());
				sb.append("</td><td style='mso-number-format:\"\\@\";'>");
				sb.append(user.getBuildingNo());
				sb.append("</td><td style='mso-number-format:\"\\@\";'>");
				sb.append(user.getUnitNo());
				sb.append("</td><td style='mso-number-format:\"\\@\";'>");
				sb.append(user.getRoomNo());
				sb.append("</td><td style='mso-number-format:\"\\@\";'>");
				sb.append(user.getUsername());
				sb.append("</td><td style='mso-number-format:\"\\@\";'>");
				sb.append(user.getRealName());
				sb.append("</td><td style='mso-number-format:\"\\@\";' align='right'>");
				sb.append(user.getShopPoint());
				sb.append("</td><td style='mso-number-format:\"\\@\";' align='right'>");
				sb.append(user.getAccountBalance());
				sb.append("</td><td style='mso-number-format:\"\\@\";'>");
				sb.append(user.getSex() == 0 ? "男" : "女");
				sb.append("</td><td style='mso-number-format:\"\\@\";'>");
				if (user.getBirthday() == null) {
					sb.append("&nbsp;");
				} else {
					try {
						sb.append(dtf.format(user.getBirthday()));
					} catch (Exception e) {
					}
				}
				sb.append("</td><td style='mso-number-format:\"\\@\";'>");
				sb.append(user.getTelephone());
				sb.append("</td><td style='mso-number-format:\"\\@\";'>");
				sb.append(user.getMobile());
				sb.append("</td><td style='mso-number-format:\"\\@\";'>");
				sb.append(user.getFamilyNumber());
				sb.append("</td><td style='mso-number-format:\"\\@\";'>");
				sb.append(user.getOccupation());
				sb.append("</td><td style='mso-number-format:\"\\@\";'>");
				sb.append(user.getInterest());
				sb.append("</td></tr>");
			}
			sb.append("</table>");
			return sb.toString().replaceAll("null</td>", "&nbsp;</td>"); // null替换掉
		}
		return null;

	}

	/**
	 * 查询用户.
	 * 
	 * @param userId
	 */
	public QueryResult listUser(Integer companyId, String communityId, String buildNo, String unitNo, String roomNo,
			Integer pageNumber) {

		List<CompanyToBuilding> resultList = this.dao().query(CompanyToBuilding.class,
				Cnd.where("companyId", "=", companyId)); // 当前页面显示记录
		List<Integer> builds = new ArrayList<Integer>();
		for (CompanyToBuilding ctb : resultList)
			builds.add(ctb.getBuildingId());
		// 拼接条件
		SqlExpressionGroup group = Cnd.exps("1", "=", 1);
		group = group.and("buildingId", "in", builds);
		if (communityId != null && communityId.length() > 0) {
			group = group.and("communityId", "=", communityId);
		}
		if (buildNo != null && buildNo.length() > 0) {
			group = group.and("buildingId", "=", buildNo);
		}
		if (unitNo != null && unitNo.length() > 0) {
			group = group.and("unitId", "=", unitNo);
		}
		if (roomNo != null && roomNo.length() > 0) {
			group = group.and("roomId", "=", roomNo);
		}
		group = group.and("deleteFlag", "<>", 1); //不是被删除的
		// 最大页数
		Pager pager = this.dao().createPager(pageNumber, Constants.LIST_ONE_PAGE_SIZE);

		// 查询记录总数
		int recordCount = this.dao().count(ShopUserView.class, Cnd.where(group));
		pager.setRecordCount(recordCount);

		// 没数据，直接返回
		if (recordCount == 0) {
			return new QueryResult(new ArrayList<ShopUserView>(0), pager);
		}

		// 计算页数
		int maxPageNumber = 0;
		if (recordCount % Constants.LIST_ONE_PAGE_SIZE == 0) {
			maxPageNumber = recordCount / Constants.LIST_ONE_PAGE_SIZE;
		} else {
			maxPageNumber = recordCount / Constants.LIST_ONE_PAGE_SIZE + 1;
		}
		if (pageNumber > maxPageNumber) {
			pager.setPageNumber(maxPageNumber);
		}

		List<ShopUserView> resultLists = this.dao().query(ShopUserView.class, Cnd.where(group).desc("id"), pager); // 当前页面显示记录
		// 返回
		return new QueryResult(resultLists, pager);
	}

	/**
	 * 查询校区用户.
	 * 
	 * @param userId
	 */
	public QueryResult listSchoolUser(Integer companyId, String communityId, String oneCardNo, String username,
			String mobile, String status, Integer pageNumber) {

		Company company = this.dao().fetch(Company.class, companyId); // 当前页面显示记录
		Community communityReal = this.dao().fetch(Community.class, company.getCommunityId());

		// 拼接条件
		SqlExpressionGroup group = Cnd.exps("1", "=", 1);
		//		group = group.and("buildingId", "in", builds);
		if (communityId != null && communityId.length() > 0) {
			group = group.and("communityId", "=", communityId);
		} else
			group = group.and("communityId", "=", communityReal.getId());
		if (oneCardNo != null && oneCardNo.length() > 0) {
			group = group.and("oneCardNo", "like", "%" + oneCardNo.trim() + "%");
		}
		if (username != null && username.length() > 0) {
			group = group.and("username", "like", "%" + username.trim() + "%");
		}
		if (mobile != null && mobile.length() > 0) {
			group = group.and("mobile", "like", "%" + mobile.trim() + "%");
		}
		// 订单状态
		if (status != null && status.length() > 0) {
			if ("1".equals(status))
				group = group.and("status", "=", 1);
			else
				group = group.and("status", "<>", 1).or("status", "=", null);
		}
		group = group.and("deleteFlag", "<>", 1); //不是被删除的
		// 最大页数
		Pager pager = this.dao().createPager(pageNumber, Constants.LIST_ONE_PAGE_SIZE);

		// 查询记录总数
		int recordCount = this.dao().count(ShopUserView.class, Cnd.where(group));
		pager.setRecordCount(recordCount);

		// 没数据，直接返回
		if (recordCount == 0) {
			return new QueryResult(new ArrayList<ShopUserView>(0), pager);
		}

		// 计算页数
		int maxPageNumber = 0;
		if (recordCount % Constants.LIST_ONE_PAGE_SIZE == 0) {
			maxPageNumber = recordCount / Constants.LIST_ONE_PAGE_SIZE;
		} else {
			maxPageNumber = recordCount / Constants.LIST_ONE_PAGE_SIZE + 1;
		}
		if (pageNumber > maxPageNumber) {
			pager.setPageNumber(maxPageNumber);
		}

		List<ShopUserView> resultLists = this.dao().query(ShopUserView.class, Cnd.where(group).desc("id"), pager); // 当前页面显示记录
		// 返回
		return new QueryResult(resultLists, pager);
	}

}
